import Database from 'better-sqlite3';
import path from 'path';

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

// 设备类型与参数表映射关系
interface DeviceTypeMapping {
  classification_code: string;
  classification_name: string;
  specialty: string;
  naming_table: string;
  position_table: string;
  base_params_table: string;
  tech_spec_table?: string;
}

// 根据现有数据和参数表创建映射关系
const deviceTypeMappings: DeviceTypeMapping[] = [
  // 配管专业
  {
    classification_code: "L4-06-01-01-01-000",
    classification_name: "离心泵",
    specialty: "配管专业",
    naming_table: "table_c2",
    position_table: "table_c4",
    base_params_table: "table_c18"
  },
  {
    classification_code: "L4-06-01-01-02-000", 
    classification_name: "转子泵",
    specialty: "配管专业",
    naming_table: "table_c2",
    position_table: "table_c4", 
    base_params_table: "table_c27"
  },
  {
    classification_code: "L4-06-01-01-03-000",
    classification_name: "滑片泵", 
    specialty: "配管专业",
    naming_table: "table_c2",
    position_table: "table_c4",
    base_params_table: "table_c30"
  },
  {
    classification_code: "L4-06-01-01-04-000",
    classification_name: "潜液泵",
    specialty: "配管专业", 
    naming_table: "table_c2",
    position_table: "table_c4",
    base_params_table: "table_c36"
  },
  {
    classification_code: "L4-06-02-01-01-000",
    classification_name: "圆筒形储罐",
    specialty: "配管专业",
    naming_table: "table_c2", 
    position_table: "table_c4",
    base_params_table: "table_c81"
  },
  {
    classification_code: "L4-06-02-02-01-000",
    classification_name: "鹤管",
    specialty: "配管专业",
    naming_table: "table_c2",
    position_table: "table_c4", 
    base_params_table: "table_c109_1"
  },
  // 仪表专业
  {
    classification_code: "L4-07-01-01-01-000",
    classification_name: "温度计",
    specialty: "仪表专业",
    naming_table: "table_c2",
    position_table: "table_c4",
    base_params_table: "table_c271"
  },
  {
    classification_code: "L4-07-01-01-02-000", 
    classification_name: "温度变送器",
    specialty: "仪表专业",
    naming_table: "table_c2",
    position_table: "table_c4",
    base_params_table: "table_c273"
  },
  {
    classification_code: "L4-07-01-02-01-000",
    classification_name: "压力表", 
    specialty: "仪表专业",
    naming_table: "table_c2",
    position_table: "table_c4",
    base_params_table: "table_c277"
  },
  {
    classification_code: "L4-07-01-02-02-000",
    classification_name: "压力变送器",
    specialty: "仪表专业",
    naming_table: "table_c2", 
    position_table: "table_c4",
    base_params_table: "table_c281"
  },
  // 电气专业
  {
    classification_code: "L4-08-01-01-01-000",
    classification_name: "燃油发电设备",
    specialty: "电气专业",
    naming_table: "table_c2",
    position_table: "table_c4",
    base_params_table: "table_c363"
  },
  {
    classification_code: "L4-08-01-02-01-000",
    classification_name: "电力变压器", 
    specialty: "电气专业",
    naming_table: "table_c2",
    position_table: "table_c4",
    base_params_table: "table_c376"
  },
  {
    classification_code: "L4-08-01-03-01-000",
    classification_name: "低压开关柜",
    specialty: "电气专业", 
    naming_table: "table_c2",
    position_table: "table_c4",
    base_params_table: "table_c385"
  },
  // 通信专业
  {
    classification_code: "L4-09-01-01-01-000",
    classification_name: "计算机网络设备",
    specialty: "通信专业",
    naming_table: "table_c2",
    position_table: "table_c4", 
    base_params_table: "table_c413"
  },
  {
    classification_code: "L4-09-01-02-01-000",
    classification_name: "火灾自动报警设备",
    specialty: "通信专业",
    naming_table: "table_c2",
    position_table: "table_c4",
    base_params_table: "table_c416"
  }
];

function createDeviceTypeMappingTable() {
  // 创建设备类型映射表
  const createTableSQL = `
    CREATE TABLE IF NOT EXISTS device_type_mappings (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      classification_code TEXT NOT NULL UNIQUE,
      classification_name TEXT NOT NULL,
      specialty TEXT NOT NULL,
      naming_table TEXT NOT NULL,
      position_table TEXT NOT NULL,
      base_params_table TEXT NOT NULL,
      tech_spec_table TEXT,
      created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
  `;
  
  db.exec(createTableSQL);
  
  // 创建索引
  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_device_type_mappings_classification_code 
    ON device_type_mappings(classification_code);
    CREATE INDEX IF NOT EXISTS idx_device_type_mappings_specialty 
    ON device_type_mappings(specialty);
  `);
  
  console.log('✅ 创建设备类型映射表');
}

function insertDeviceTypeMappings() {
  const insertSQL = `
    INSERT OR REPLACE INTO device_type_mappings 
    (classification_code, classification_name, specialty, naming_table, position_table, base_params_table, tech_spec_table)
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `;
  
  const stmt = db.prepare(insertSQL);
  
  let insertedCount = 0;
  for (const mapping of deviceTypeMappings) {
    stmt.run(
      mapping.classification_code,
      mapping.classification_name,
      mapping.specialty,
      mapping.naming_table,
      mapping.position_table,
      mapping.base_params_table,
      mapping.tech_spec_table || null
    );
    insertedCount++;
  }
  
  console.log(`✅ 插入了 ${insertedCount} 个设备类型映射`);
}

function updateDevicesTableStructure() {
  // 更新设备表，将表ID字段改为实际的记录ID引用
  const updateSQL = `
    ALTER TABLE devices ADD COLUMN naming_record_id INTEGER;
    ALTER TABLE devices ADD COLUMN position_record_id INTEGER;
    ALTER TABLE devices ADD COLUMN base_params_record_id INTEGER;
    ALTER TABLE devices ADD COLUMN tech_spec_record_id INTEGER;
  `;
  
  try {
    // 分别执行每个 ALTER TABLE 语句
    db.exec('ALTER TABLE devices ADD COLUMN naming_record_id INTEGER');
    console.log('✅ 添加 naming_record_id 字段');
  } catch (error) {
    console.log('ℹ️ naming_record_id 字段已存在');
  }
  
  try {
    db.exec('ALTER TABLE devices ADD COLUMN position_record_id INTEGER');
    console.log('✅ 添加 position_record_id 字段');
  } catch (error) {
    console.log('ℹ️ position_record_id 字段已存在');
  }
  
  try {
    db.exec('ALTER TABLE devices ADD COLUMN base_params_record_id INTEGER');
    console.log('✅ 添加 base_params_record_id 字段');
  } catch (error) {
    console.log('ℹ️ base_params_record_id 字段已存在');
  }
  
  try {
    db.exec('ALTER TABLE devices ADD COLUMN tech_spec_record_id INTEGER');
    console.log('✅ 添加 tech_spec_record_id 字段');
  } catch (error) {
    console.log('ℹ️ tech_spec_record_id 字段已存在');
  }
}

async function main() {
  try {
    console.log('🚀 开始创建设备类型映射系统...');
    
    // 1. 创建设备类型映射表
    createDeviceTypeMappingTable();
    
    // 2. 插入映射数据
    insertDeviceTypeMappings();
    
    // 3. 更新设备表结构
    updateDevicesTableStructure();
    
    // 4. 验证数据
    const mappings = db.prepare('SELECT * FROM device_type_mappings ORDER BY specialty, classification_name').all();
    console.log('\n📋 设备类型映射列表:');
    mappings.forEach((mapping: any) => {
      console.log(`  ${mapping.specialty} - ${mapping.classification_name}: ${mapping.base_params_table}`);
    });
    
    console.log(`\n✅ 设备类型映射系统创建完成!`);
    console.log(`📊 总计创建了 ${mappings.length} 个设备类型映射`);
    
  } catch (error) {
    console.error('❌ 创建设备类型映射系统失败:', error);
    process.exit(1);
  } finally {
    db.close();
  }
}

main();
